﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Transactions;


using Global.Class.Library;
using DataTransferObject;
using DataAccessLayer;
using DataAccessLayer.InMarketDTSTableAdapters;
using DataAccessLayer.ERmgrUIPTableAdapters;

namespace BusinessLogicLayer
{
    public class InMarketBLL : NotifyPropertyChangeObject
    {
        public GlobalEnum.TaskID TaskID { get { return GlobalEnum.TaskID.InMarket; } }

        private UserInformation userOrganization;

        private InMarketMaster inMarketMaster;

        private BindingList<InMarketDetail> inMarketDetailList;



        public InMarketBLL()
        {
            try
            {
                if (GlobalVariables.shouldRestoreProcedure) RestoreProcedure();

                userOrganization = new UserInformation();

                inMarketMaster = new InMarketMaster();

                this.inMarketDetailList = new BindingList<InMarketDetail>();

                GlobalDefaultValue.Apply(this);


                this.InMarketMaster.PropertyChanged += new PropertyChangedEventHandler(InMarketMaster_PropertyChanged);

                this.InMarketDetailList.ListChanged += new ListChangedEventHandler(InMarketDetail_ListChanged);

            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        private void InMarketMaster_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            this.SetDirty();
        }

        private void InMarketDetail_ListChanged(object sender, ListChangedEventArgs e)
        {
            this.SetDirty();
        }




        #region <Adapter>

        private InMarketListingTableAdapter listingTableAdapter;
        protected InMarketListingTableAdapter ListingTableAdapter
        {
            get
            {
                if (listingTableAdapter == null) listingTableAdapter = new InMarketListingTableAdapter();
                return listingTableAdapter;
            }
        }

        private InMarketMasterTableAdapter masterTableAdapter;
        protected InMarketMasterTableAdapter MasterTableAdapter
        {
            get
            {
                if (masterTableAdapter == null) masterTableAdapter = new InMarketMasterTableAdapter();
                return masterTableAdapter;
            }
        }

        private InMarketDetailTableAdapter detailTableAdapter;
        protected InMarketDetailTableAdapter DetailTableAdapter
        {
            get
            {
                if (detailTableAdapter == null) detailTableAdapter = new InMarketDetailTableAdapter();
                return detailTableAdapter;
            }
        }

        
        #endregion <Adapter>

        #region <Storage>

        public UserInformation UserOrganization
        {
            get { return this.userOrganization; }
            set { this.userOrganization = value; }
        }

        public InMarketDTS.InMarketListingDataTable InMarketListing(DateTime lowerFillterDate, DateTime upperFillterDate)
        {
            return this.ListingTableAdapter.GetData(lowerFillterDate, upperFillterDate);
        }

        public InMarketMaster InMarketMaster
        {
            get { return this.inMarketMaster; }
        }

        public BindingList<InMarketDetail> InMarketDetailList
        {
            get { return this.inMarketDetailList; }
        }


        public double TotalQuantity
        {
            get { return this.inMarketDetailList.Sum(q => q.Quantity); }
        }

        public double TotalAmountPrimitive
        {
            get { return this.inMarketDetailList.Sum(a => a.AmountPrimitive); }
        }

        public double TotalAmountDiscount
        {
            get { return this.inMarketDetailList.Sum(a => a.AmountDiscount); }
        }

        public double TotalAmountActual
        {
            get { return this.inMarketDetailList.Sum(a => a.AmountActual); }
        }


        #endregion <Storage>

        #region Properties

        #region <Primary Key>

        public int InMarketID   //Primary Key
        {
            get { return this.InMarketMaster.InMarketID; }
            private set
            {
                if (this.InMarketMaster.InMarketID != value)
                {
                    this.StopTracking();

                    this.InMarketMaster.InMarketID = value;

                    this.InMarketGetMaster();
                    this.InMarketGetDetail();

                    this.StartTracking();
                    this.Reset();
                }

            }
        }

        #endregion <Primary Key>

        private void InMarketGetMaster()
        {
            if (this.InMarketID > 0)
            {
                InMarketDTS.InMarketMasterDataTable masterDataTable = this.MasterTableAdapter.GetData(this.InMarketID);

                if (masterDataTable.Count > 0)
                {
                    this.InMarketMaster.StopTracking();

                    this.InMarketMaster.OrderOrReturnID = masterDataTable[0].OrderOrReturnID;

                    this.InMarketMaster.CustomerID = masterDataTable[0].CustomerID;
                    this.InMarketMaster.CustomerValue = masterDataTable[0].CustomerValue;

                    this.InMarketMaster.DistributorID = masterDataTable[0].DistributorID;
                    this.InMarketMaster.SalesmenID = masterDataTable[0].SalesmenID;

                    this.InMarketMaster.PromotionCampaignID = masterDataTable[0].PromotionCampaignID;
                    this.InMarketMaster.ReturnCategoryID = masterDataTable[0].ReturnCategoryID;
                    this.InMarketMaster.RefuseCategoryID = masterDataTable[0].RefuseCategoryID;

                    this.InMarketMaster.ImportBatchID = masterDataTable[0].ImportBatchID;
                    
                    this.InMarketMaster.InMarketDate = masterDataTable[0].InMarketDate;
                    this.InMarketMaster.EntryDate = masterDataTable[0].EntryDate;
                    
                    this.InMarketMaster.Reference = masterDataTable[0].Reference;
                    this.InMarketMaster.Description = masterDataTable[0].Description;
                    this.InMarketMaster.Other = masterDataTable[0].Other;

                    this.InMarketMaster.StartTracking();

                    this.InMarketMaster.Reset();

                    this.UserOrganization.UserID = masterDataTable[0].UserID;
                    this.UserOrganization.UserOrganizationID = masterDataTable[0].UserOrganizationID;
                }
                else throw new System.ArgumentException("Insufficient get data");
            }
            else
            {
                GlobalDefaultValue.Apply(this.InMarketMaster);
                this.InMarketMaster.InMarketDate = DateTime.Today;
                this.InMarketMaster.EntryDate = DateTime.Today;
                this.InMarketMaster.Reset();
            }
        }


        private void InMarketGetDetail()
        {
            this.inMarketDetailList.RaiseListChangedEvents = false;
            this.inMarketDetailList.Clear();
            if (this.InMarketID >= 0)
            {

                InMarketDTS.InMarketDetailDataTable detailDataTable = this.DetailTableAdapter.GetData(this.InMarketID);

                if (detailDataTable.Count > 0)
                {
                    foreach (InMarketDTS.InMarketDetailRow detailRow in detailDataTable.Rows)
                    {
                        this.inMarketDetailList.Add(new InMarketDetail(detailRow.CollectedTypeID, detailRow.CommodityID, detailRow.Quantity, detailRow.UPrice, detailRow.UPriceEX, detailRow.UPriceOD, detailRow.UPriceCO, detailRow.DiscountPercent, ""));
                    }
                }
            }
            this.inMarketDetailList.RaiseListChangedEvents = true;
            this.inMarketDetailList.ResetBindings();
        }

        
        #endregion Properties





        #region Object Permission

        public override bool IsValid
        {
            get
            {
                List<InMarketDetail> inValidInMarketDetail = this.InMarketDetailList.Where(inMarketDetail => !inMarketDetail.IsValid).ToList();
                return this.InMarketMaster.IsValid && inValidInMarketDetail.Count == 0;
            }
        }

        public bool ReadOnly
        {
            get
            {
                try
                {
                    return GlobalUserPermission.GetUserReadOnly(GlobalVariables.GlobalUserInformation.UserID, this.TaskID);
                }
                catch
                {
                    return true;
                }
            }
        }

        public bool Editable
        {
            get
            {
                try
                {
                    if (this.inMarketMaster.InMarketDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, this.TaskID, this.UserOrganization.UserOrganizationID)) return false;

                    if (!GlobalUserPermission.GetEditable("InMarketApproved", this.InMarketID)) return false;

                    return GlobalUserPermission.GetEditable("InMarketEditable", this.InMarketID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Verifiable
        {
            get
            {
                try
                {
                    if (this.inMarketMaster.InMarketDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.InMarketVerifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("InMarketEditable", this.InMarketID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Unverifiable
        {
            get
            {
                try
                {
                    if (this.inMarketMaster.InMarketDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.InMarketUnverifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("InMarketEditable", this.InMarketID);
                }
                catch
                {
                    return false;
                }
            }
        }


        #endregion Object Permission






        public void Fill(int inMarketID)
        {
            if (this.InMarketID == inMarketID) this.InMarketID = -1; //Enforce to reload
            this.InMarketID = inMarketID;
        }

        public void New()
        {
            if (this.InMarketID == 0) this.InMarketID = -1;
            this.InMarketID = 0;
        }

        public void Edit()
        {

        }

        #region Save & Delete Method


        public bool Save()
        {
            int inMarketID = 0;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient save", "Save validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient save", "Uneditable");

                    if (!this.SaveMaster(ref inMarketID)) throw new System.ArgumentException("Insufficient save", "Save master");

                    if (!this.SaveDetail(inMarketID)) throw new System.ArgumentException("Insufficient save", "Save detail");

                    if (this.SaveConflict()) throw new System.ArgumentException("Insufficient save", "Save conflict");

                    transactionScope.Complete();                    
                }

                this.Fill(inMarketID);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        public bool Delete()
        {
            if (this.inMarketMaster.InMarketID <= 0) return false;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient delete", "Delete validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient delete", "Uneditable");

                    if (!this.SaveUndo(this.inMarketMaster.InMarketID)) throw new System.ArgumentException("Insufficient delete", "Delete detail");

                    if (this.MasterTableAdapter.Delete(this.inMarketMaster.InMarketID) != 1) throw new System.ArgumentException("Insufficient delete", "Delete master");

                    if (this.SaveConflict()) throw new System.ArgumentException("Insufficient delete", "Delete conflict");

                    transactionScope.Complete();                    
                }
                this.Fill(0);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        private bool SaveValidate()
        {
            ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

            this.UserOrganization = GlobalUserPermission.GetUserInformation(this.UserOrganization.UserID, this.InMarketMaster.InMarketDate);

            if (this.UserOrganization.UserID <= 0 || this.UserOrganization.UserOrganizationID <= 0) exceptionTable.AddException(new string[] { GlobalVariables.stringFieldRequired, "User information" });

            if (exceptionTable.Table.Rows.Count <= 0 && this.IsValid)
                return true;
            else
            {
                exceptionTable.AddException(new string[] { "Reference: ", this.InMarketMaster.Reference + this.InMarketMaster.InMarketDate.ToString() });

                if (!this.InMarketMaster.IsValid) exceptionTable.AddException(new string[] { GlobalVariables.stringFieldRequired, this.InMarketMaster.Error });


                List<InMarketDetail> inValidInMarketDetail = this.InMarketDetailList.Where(inMarketDetail => !inMarketDetail.IsValid).ToList();
                
                if ( inValidInMarketDetail.Count != 0)
                    foreach( InMarketDetail a in inValidInMarketDetail)
                        exceptionTable.AddException(new string[] { GlobalVariables.stringFieldRequired, a.Error });

                
                throw new CustomException("Save validate", exceptionTable.Table);
            }
        }


        private bool SaveMaster(ref int inMarketID)
        {
            InMarketDTS.InMarketMasterDataTable masterDataTable;
            InMarketDTS.InMarketMasterRow masterRow;

            if (this.inMarketMaster.InMarketID <= 0) //Add
            {
                masterDataTable = new InMarketDTS.InMarketMasterDataTable();
                masterRow = masterDataTable.NewInMarketMasterRow();
            }
            else //Edit
            {
                if (!this.SaveUndo(inMarketMaster.InMarketID)) throw new System.ArgumentException("Insufficient save", "Save undo");
                masterDataTable = this.MasterTableAdapter.GetData(inMarketMaster.InMarketID);
                if (masterDataTable.Count > 0) masterRow = masterDataTable[0]; else throw new System.ArgumentException("Insufficient save", "Get for edit");
            }

            masterRow.OrderOrReturnID = this.inMarketMaster.OrderOrReturnID;

            masterRow.CustomerID = this.inMarketMaster.CustomerID;
            masterRow.CustomerValue = this.inMarketMaster.CustomerValue;

            masterRow.DistributorID = this.inMarketMaster.DistributorID;
            masterRow.SalesmenID = this.inMarketMaster.SalesmenID;

            masterRow.PromotionCampaignID = this.inMarketMaster.PromotionCampaignID;
            masterRow.ReturnCategoryID = this.inMarketMaster.ReturnCategoryID;
            masterRow.RefuseCategoryID = this.inMarketMaster.RefuseCategoryID;

            masterRow.ImportBatchID = this.inMarketMaster.ImportBatchID;

            masterRow.InMarketDate = this.inMarketMaster.InMarketDate;
            masterRow.EntryDate = this.inMarketMaster.EntryDate;

            masterRow.Reference = this.inMarketMaster.Reference;
            masterRow.Description = this.inMarketMaster.Description;
            masterRow.Other = this.inMarketMaster.Other;

            masterRow.TotalQuantity = this.TotalQuantity ;
            masterRow.TotalAmountPrimitive = this.TotalAmountPrimitive;
            masterRow.TotalAmountDiscount = this.TotalAmountDiscount;
            masterRow.TotalAmountActual = this.TotalAmountActual;
            masterRow.Approved = false;
            masterRow.InActive = false;


            ListCustomerNameTableAdapter listCustomerNameTableAdapter = new ListCustomerNameTableAdapter();
            ERmgrUIP.ListCustomerNameDataTable listCustomerNameDataTable = listCustomerNameTableAdapter.GetDataByCustomerID(this.inMarketMaster.CustomerID);
            if (listCustomerNameDataTable.Rows.Count > 0)
            {
                masterRow.CustomerCategoryID = ((ERmgrUIP.ListCustomerNameRow)listCustomerNameDataTable.Rows[0]).CustomerCategoryID;
                masterRow.CustomerChannelID = ((ERmgrUIP.ListCustomerNameRow)listCustomerNameDataTable.Rows[0]).CustomerChannelID;
                masterRow.CustomerTypeID = ((ERmgrUIP.ListCustomerNameRow)listCustomerNameDataTable.Rows[0]).CustomerTypeID;
                masterRow.CustomerGroupID = ((ERmgrUIP.ListCustomerNameRow)listCustomerNameDataTable.Rows[0]).CustomerGroupID;
                masterRow.CustomerAddressAreaID = ((ERmgrUIP.ListCustomerNameRow)listCustomerNameDataTable.Rows[0]).AddressAreaID;
            }

            ListDistributorNameTableAdapter listDistributorNameTableAdapter = new ListDistributorNameTableAdapter();
            ERmgrUIP.ListDistributorNameDataTable listDistributorNameDataTable = listDistributorNameTableAdapter.GetDataByDistributorID(this.inMarketMaster.DistributorID);
            if (listDistributorNameDataTable.Rows.Count > 0)
            {
                masterRow.DistributorCategoryID = ((ERmgrUIP.ListDistributorNameRow)listDistributorNameDataTable.Rows[0]).DistributorCategoryID;
                masterRow.DistributorTypeID = ((ERmgrUIP.ListDistributorNameRow)listDistributorNameDataTable.Rows[0]).DistributorTypeID;
                masterRow.DistributorAddressAreaID = ((ERmgrUIP.ListDistributorNameRow)listDistributorNameDataTable.Rows[0]).AddressAreaID;
            }

            ListSalesmenNameTableAdapter listSalesmenNameTableAdapter = new ListSalesmenNameTableAdapter();
            ERmgrUIP.ListSalesmenNameDataTable listSalesmenNameDataTable = listSalesmenNameTableAdapter.GetDataBySalesmenID(this.inMarketMaster.SalesmenID);
            if (listSalesmenNameDataTable.Rows.Count > 0)
            {
                masterRow.SalesmenCategoryID = ((ERmgrUIP.ListSalesmenNameRow)listSalesmenNameDataTable.Rows[0]).SalesmenCategoryID;
                masterRow.SalesmenTypeID = ((ERmgrUIP.ListSalesmenNameRow)listSalesmenNameDataTable.Rows[0]).SalesmenTypeID;
                masterRow.SalesmenAddressAreaID = ((ERmgrUIP.ListSalesmenNameRow)listSalesmenNameDataTable.Rows[0]).AddressAreaID;
            }


            masterRow.UserID = this.UserOrganization.UserID;
            masterRow.UserOrganizationID = this.UserOrganization.UserOrganizationID;

            if (this.inMarketMaster.InMarketID <= 0) masterDataTable.AddInMarketMasterRow(masterRow);

            int rowsAffected = this.MasterTableAdapter.Update(masterRow);

            inMarketID = masterRow.InMarketID;

            return rowsAffected == 1;

        }


        private bool SaveDetail(int inMarketID)
        {
            int serialID = 0; int rowsAffected = 0;


            #region <Save detail>

            serialID = 0;

            InMarketDTS.InMarketDetailDataTable detailDataTable = new InMarketDTS.InMarketDetailDataTable();

            foreach (InMarketDetail inMarketDetail in this.inMarketDetailList)
            {
                InMarketDTS.InMarketDetailRow detailRow = detailDataTable.NewInMarketDetailRow();

                detailRow.InMarketID = inMarketID;
                detailRow.SerialID = ++serialID;

                detailRow.CollectedTypeID = inMarketDetail.CollectedTypeID;
                detailRow.CommodityID = inMarketDetail.CommodityID;

                detailRow.Quantity = inMarketDetail.Quantity;
                detailRow.UPrice = inMarketDetail.UPrice;
                detailRow.UPriceEX = inMarketDetail.UPriceEX;
                detailRow.UPriceOD = inMarketDetail.UPriceOD;
                detailRow.UPriceCO = inMarketDetail.UPriceCO;
                detailRow.DiscountPercent = inMarketDetail.DiscountPercent;
                detailRow.UPriceDiscount = inMarketDetail.UPriceDiscount;
                detailRow.AmountPrimitive = inMarketDetail.AmountPrimitive;
                detailRow.AmountDiscount = inMarketDetail.AmountDiscount;
                detailRow.AmountActual = inMarketDetail.AmountActual;
                
                detailDataTable.AddInMarketDetailRow(detailRow);
            }

            rowsAffected = this.DetailTableAdapter.Update(detailDataTable);
            if (rowsAffected != this.inMarketDetailList.Count) throw new System.ArgumentException("Insufficient save", "Save detail: Item Category");

            #endregion <Save detail>


            return true;
        }


        private bool SaveUndo(int inMarketID)
        {
            this.DetailTableAdapter.Delete(inMarketID);

            return true;
        }


        private bool SaveConflict()
        {
            return false;
        }



        #endregion



        #region Import Excel

        public bool ImportExcel(string fileName)
        {
            try
            {
                DateTime dateTimeValue; string inMarketReference = "";
                ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

                TimeSpan timeout = TimeSpan.FromMinutes(90);
                using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required, timeout))
                {
                    if (this.ReadOnly) throw new System.ArgumentException("Import", "Permission conflict");

                    DataTable excelDataTable = OleDbDatabase.OpenExcelSheet(OleDbDatabase.MappingTaskID.InMarket, fileName);
                    if (excelDataTable != null && excelDataTable.Rows.Count > 0)
                    {
                        string importInMarketID = "";
                        InMarketBLL importInMarketBLL = new InMarketBLL();

                        foreach (DataRow excelDataRow in excelDataTable.Rows)
                        {
                            if (importInMarketID != excelDataRow["InMarketID"].ToString().Trim())
                            {//Start a new importInMarketBLL
                                importInMarketID = excelDataRow["InMarketID"].ToString().Trim();

                                //Save current importInMarketBLL if existing
                                if (!exceptionTable.IsDirty)
                                    if (importInMarketBLL.IsDirty && !importInMarketBLL.Save())
                                        exceptionTable.AddException(new string[] { "Insufficient save", inMarketReference });

                                //Start a new importInMarketBLL
                                importInMarketBLL.New();
                                exceptionTable.ClearDirty();



                                //ERmgrVCP.ListInMarketTypeRow gcgbCommandTypeRow = CommonDatabaseMethod.GetMarketingPaymentType(0, excelDataRow["MarketingPaymentTypeID"].ToString().Trim());
                                //if (gcgbCommandTypeRow != null)
                                //    importGCGBCommandBLL.gcgbCommandMaster.GCGBCommandTypeID = gcgbCommandTypeRow.GCGBCommandTypeID;
                                //else
                                //{//Handle exception when MarketingPaymentType can not be found
                                //    exceptionTable.AddException(new string[] { "Payment Type", excelDataRow["GCGBCommandTypeID"].ToString() });
                                //}


                                if (DateTime.TryParse(excelDataRow["GCGBCommandDate"].ToString(), out dateTimeValue)) importInMarketBLL.inMarketMaster.InMarketDate = dateTimeValue; else exceptionTable.AddException(new string[] { "Invalid Date Value", excelDataRow["RequestedDate"].ToString() });
                                if (DateTime.TryParse(excelDataRow["EntryDate"].ToString(), out dateTimeValue)) importInMarketBLL.inMarketMaster.EntryDate = dateTimeValue; else exceptionTable.AddException(new string[] { "Invalid Date Value", excelDataRow["EntryDate"].ToString() });

                                importInMarketBLL.inMarketMaster.Reference = excelDataRow["GCGBCommandReference"].ToString();
                                importInMarketBLL.inMarketMaster.Description = excelDataRow["Description"].ToString();

                                ////////ERmgrVCP.SPGetSalesmenNameRow salesmenNameRow = CommonDatabaseMethod.GetSalesmenName(0, excelDataRow["PreparedEmployeeID"].ToString().Trim());
                                ////////if (salesmenNameRow != null)
                                ////////    importGCGBCommandBLL.inMarketMaster.PreparedEmployeeID = salesmenNameRow.SalesmenID;
                                ////////else
                                ////////{//Handle exception when ItemType can not be found
                                ////////    exceptionTable.AddException(new string[] { "Staffs", excelDataRow["PreparedEmployeeID"].ToString() });
                                ////////}


                                ////////salesmenNameRow = CommonDatabaseMethod.GetSalesmenName(0, excelDataRow["ApprovedEmployeeID"].ToString().Trim());
                                ////////if (salesmenNameRow != null)
                                ////////    importGCGBCommandBLL.inMarketMaster.ApprovedEmployeeID = salesmenNameRow.SalesmenID;
                                ////////else
                                ////////{//Handle exception when ItemType can not be found
                                ////////    exceptionTable.AddException(new string[] { "Staffs", excelDataRow["ApprovedEmployeeID"].ToString() });
                                ////////}

                                importInMarketBLL.inMarketMaster.Other = excelDataRow["Remarks"].ToString();
                            }

                            #region Import Detail detail

                            //ERmgrVCP.SPGetCustomerNameRow customerNameRow = CommonDatabaseMethod.GetCustomerName(0, excelDataRow["CustomerID"].ToString().Trim());
                            //if (customerNameRow != null)
                            //{
                            //    ERmgrVCP.SPGetMarketingPaymentTermRow marketingPaymentTermRow = CommonDatabaseMethod.GetMarketingPaymentTerm(0, excelDataRow["MarketingPaymentTermID"].ToString().Trim());
                            //    if (marketingPaymentTermRow != null)
                            //        importGCGBCommandBLL.GCGBCommandCollectionList.Add(new GCGBCommandCollection(customerNameRow.CustomerID, 0, marketingPaymentTermRow.MarketingPaymentTermID, (double)excelDataRow["TurnOverAmount"], (double)excelDataRow["IncentiveAmount"], 0, 0, 0, 0, 0, 0, excelDataRow["DetailRemarks"].ToString().Trim()));
                            //    else
                            //    {//Handle exception when MarketingPaymentTerm can not be found
                            //        exceptionTable.AddException(new string[] { "Payment Term", excelDataRow["MarketingPaymentTermID"].ToString() });
                            //    }
                            //}

                            //else
                            //{//Handle exception when CustomerName can not be found
                            //    exceptionTable.AddException(new string[] { "Customer", excelDataRow["CustomerID"].ToString() });
                            //}

                            #endregion Import Detail detail

                        }

                        //Save the last importGCGBCommandBLL after loop for all excelDataRow
                        if (!exceptionTable.IsDirty)
                            if (importInMarketBLL.IsDirty && !importInMarketBLL.Save())
                                exceptionTable.AddException(new string[] { "Insufficient save", inMarketReference });
                        //--------------OK

                    }
                    if (exceptionTable.Table.Rows.Count <= 0)
                        transactionScope.Complete();
                    else
                        throw new CustomException("Invalid data value or data entry not found", exceptionTable.Table);

                }
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }

        }

        #endregion Import Excel



        private void RestoreProcedure()
        {
            string queryString;
            string[] queryArray;
            //SET IDENTITY_INSERT  database_name .  schema_name .  table { ON | OFF }
            //http://msdn.microsoft.com/en-us/library/ms188059(v=sql.100).aspx



            queryString = "     @LowerFillterDate DateTime, @UpperFillterDate DateTime " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      LEFT(CONVERT(char(10), InMarketMaster.InMarketDate, 111), 7) AS InMarketMonth, InMarketMaster.InMarketID, InMarketMaster.Reference, InMarketMaster.InMarketDate, InMarketMaster.TotalAmountActual, " + "\r\n";
            queryString = queryString + "                   ListCustomerName.DescriptionOfficial + ', ' + ListCustomerName.AddressNo + ' ' + ListCustomerName.AddressStreet AS CustomerName, ListDistributorName.DescriptionOfficial AS DistributorName, ListSalesmenName.DescriptionLastName AS SalesmenName " + "\r\n";

            queryString = queryString + "       FROM        InMarketMaster INNER JOIN  " + "\r\n";
            queryString = queryString + "                   ListCustomerName ON InMarketMaster.CustomerID = ListCustomerName.CustomerID INNER JOIN  " + "\r\n";
            queryString = queryString + "                   ListDistributorName ON InMarketMaster.DistributorID = ListDistributorName.DistributorID INNER JOIN " + "\r\n";
            queryString = queryString + "                   ListSalesmenName ON InMarketMaster.SalesmenID = ListSalesmenName.SalesmenID " + "\r\n";
            queryString = queryString + "       WHERE       InMarketMaster.InMarketDate >= @LowerFillterDate AND InMarketMaster.InMarketDate <= @UpperFillterDate " + "\r\n";

            SQLDatabase.CreateStoredProcedure("InMarketListing", queryString);





            queryString = "     @InMarketID Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      InMarketID, Reference, InMarketDate, EntryDate, OrderOrReturnID, CustomerID, CustomerValue, DistributorID, SalesmenID, PromotionCampaignID, ReturnCategoryID, RefuseCategoryID, Description, Other, ImportBatchID, CustomerCategoryID, CustomerTypeID, CustomerGroupID, CustomerChannelID, CustomerAddressAreaID, DistributorCategoryID, DistributorTypeID, DistributorAddressAreaID, SalesmenCategoryID, SalesmenTypeID, SalesmenAddressAreaID, TotalQuantity, TotalAmountPrimitive, TotalAmountDiscount, TotalAmountActual, UserID, UserOrganizationID, InActive, Approved " + "\r\n";
            queryString = queryString + "       FROM        InMarketMaster " + "\r\n";
            queryString = queryString + "       WHERE       InMarketID = @InMarketID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("InMarketMasterSelect", queryString);


            queryString = "     @Reference nvarchar(60), @InMarketDate datetime, @EntryDate datetime, @OrderOrReturnID int, @CustomerID int, @CustomerValue nvarchar(100), @DistributorID int, @SalesmenID int,	@PromotionCampaignID int, @ReturnCategoryID int, @RefuseCategoryID int,	@Description nvarchar(100),	@Other nvarchar(100), @ImportBatchID int, @CustomerCategoryID int, @CustomerTypeID int,	@CustomerGroupID int, @CustomerChannelID int, @CustomerAddressAreaID int, @DistributorCategoryID int, @DistributorTypeID int, @DistributorAddressAreaID int, @SalesmenCategoryID int, @SalesmenTypeID int, @SalesmenAddressAreaID int, @TotalQuantity float, @TotalAmountPrimitive float, @TotalAmountDiscount float, @TotalAmountActual float, @UserID int, @UserOrganizationID int, @InActive bit, @Approved bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO InMarketMaster (Reference, InMarketDate, EntryDate, OrderOrReturnID, CustomerID, CustomerValue, DistributorID, SalesmenID, PromotionCampaignID, ReturnCategoryID, RefuseCategoryID, Description, Other, ImportBatchID, CustomerCategoryID, CustomerTypeID, CustomerGroupID, CustomerChannelID, CustomerAddressAreaID, DistributorCategoryID, DistributorTypeID, DistributorAddressAreaID, SalesmenCategoryID, SalesmenTypeID, SalesmenAddressAreaID, TotalQuantity, TotalAmountPrimitive, TotalAmountDiscount, TotalAmountActual, UserID, UserOrganizationID, InActive, Approved) VALUES (@Reference, @InMarketDate, @EntryDate, @OrderOrReturnID, @CustomerID, @CustomerValue, @DistributorID, @SalesmenID, @PromotionCampaignID, @ReturnCategoryID, @RefuseCategoryID, @Description, @Other, @ImportBatchID, @CustomerCategoryID, @CustomerTypeID, @CustomerGroupID, @CustomerChannelID, @CustomerAddressAreaID, @DistributorCategoryID, @DistributorTypeID, @DistributorAddressAreaID, @SalesmenCategoryID, @SalesmenTypeID, @SalesmenAddressAreaID, @TotalQuantity, @TotalAmountPrimitive, @TotalAmountDiscount, @TotalAmountActual, @UserID, @UserOrganizationID, @InActive, @Approved) " + "\r\n";
            queryString = queryString + "       SELECT      InMarketID, Reference, InMarketDate, EntryDate, OrderOrReturnID, CustomerID, CustomerValue, DistributorID, SalesmenID, PromotionCampaignID, ReturnCategoryID, RefuseCategoryID, Description, Other, ImportBatchID, CustomerCategoryID, CustomerTypeID, CustomerGroupID, CustomerChannelID, CustomerAddressAreaID, DistributorCategoryID, DistributorTypeID, DistributorAddressAreaID, SalesmenCategoryID, SalesmenTypeID, SalesmenAddressAreaID, TotalQuantity, TotalAmountPrimitive, TotalAmountDiscount, TotalAmountActual, UserID, UserOrganizationID, InActive, Approved FROM InMarketMaster WHERE InMarketID = SCOPE_IDENTITY() " + "\r\n";

            SQLDatabase.CreateStoredProcedure("InMarketMasterInsert", queryString);


            queryString = "     @InMarketID int, @Reference nvarchar(60), @InMarketDate datetime, @EntryDate datetime, @OrderOrReturnID int, @CustomerID int, @CustomerValue nvarchar(100), @DistributorID int, @SalesmenID int,	@PromotionCampaignID int, @ReturnCategoryID int, @RefuseCategoryID int,	@Description nvarchar(100),	@Other nvarchar(100), @ImportBatchID int, @CustomerCategoryID int, @CustomerTypeID int, @CustomerGroupID int, @CustomerChannelID int, @CustomerAddressAreaID int, @DistributorCategoryID int, @DistributorTypeID int, @DistributorAddressAreaID int, @SalesmenCategoryID int, @SalesmenTypeID int, @SalesmenAddressAreaID int, @TotalQuantity float, @TotalAmountPrimitive float, @TotalAmountDiscount float, @TotalAmountActual float, @UserID int, @UserOrganizationID int, @InActive bit, @Approved bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      InMarketMaster SET Reference = @Reference, InMarketDate = @InMarketDate, EntryDate = @EntryDate, OrderOrReturnID = @OrderOrReturnID, CustomerID = @CustomerID, CustomerValue = @CustomerValue, DistributorID = @DistributorID, SalesmenID = @SalesmenID, PromotionCampaignID = @PromotionCampaignID, ReturnCategoryID = @ReturnCategoryID, RefuseCategoryID = @RefuseCategoryID, Description = @Description, Other = @Other, ImportBatchID = @ImportBatchID, CustomerCategoryID = @CustomerCategoryID, CustomerTypeID = @CustomerTypeID, CustomerGroupID = @CustomerGroupID, CustomerChannelID = @CustomerChannelID, CustomerAddressAreaID = @CustomerAddressAreaID, DistributorCategoryID = @DistributorCategoryID, DistributorTypeID = @DistributorTypeID, DistributorAddressAreaID = @DistributorAddressAreaID, SalesmenCategoryID = @SalesmenCategoryID, SalesmenTypeID = @SalesmenTypeID, SalesmenAddressAreaID = @SalesmenAddressAreaID, TotalQuantity = @TotalQuantity, TotalAmountPrimitive = @TotalAmountPrimitive, TotalAmountDiscount = @TotalAmountDiscount, TotalAmountActual = @TotalAmountActual, UserID = @UserID, UserOrganizationID = @UserOrganizationID, InActive = @InActive, Approved = @Approved WHERE InMarketID = @InMarketID " + "\r\n";
            queryString = queryString + "       SELECT      InMarketID, Reference, InMarketDate, EntryDate, OrderOrReturnID, CustomerID, CustomerValue, DistributorID, SalesmenID, PromotionCampaignID, ReturnCategoryID, RefuseCategoryID, Description, Other, ImportBatchID, CustomerCategoryID, CustomerTypeID, CustomerGroupID, CustomerChannelID, CustomerAddressAreaID, DistributorCategoryID, DistributorTypeID, DistributorAddressAreaID, SalesmenCategoryID, SalesmenTypeID, SalesmenAddressAreaID, TotalQuantity, TotalAmountPrimitive, TotalAmountDiscount, TotalAmountActual, UserID, UserOrganizationID, InActive, Approved FROM InMarketMaster WHERE InMarketID = @InMarketID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("InMarketMasterUpdate", queryString);


            queryString = " @InMarketID int ";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM InMarketMaster WHERE InMarketID = @InMarketID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("InMarketMasterDelete", queryString);






            queryString = " @InMarketID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      InMarketID, SerialID, CollectedTypeID, CommodityID, Quantity, UPrice, UPriceEX, UPriceOD, UPriceCO, DiscountPercent, UPriceDiscount, AmountPrimitive, AmountDiscount, AmountActual " + "\r\n";
            queryString = queryString + "       FROM        InMarketDetail " + "\r\n";
            queryString = queryString + "       WHERE       InMarketID = @InMarketID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("InMarketDetailSelect", queryString);


            queryString = " @InMarketID int, @SerialID int,	@CollectedTypeID int, @CommodityID int,	@Quantity float, @UPrice float, @UPriceEX float, @UPriceOD float, @UPriceCO float, @DiscountPercent float, @UPriceDiscount float, @AmountPrimitive float, @AmountDiscount float, @AmountActual float " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO InMarketDetail (InMarketID, SerialID, CollectedTypeID, CommodityID, Quantity, UPrice, UPriceEX, UPriceOD, UPriceCO, DiscountPercent, UPriceDiscount, AmountPrimitive, AmountDiscount, AmountActual) VALUES (@InMarketID, @SerialID, @CollectedTypeID, @CommodityID, @Quantity, @UPrice, @UPriceEX, @UPriceOD, @UPriceCO, @DiscountPercent, @UPriceDiscount, @AmountPrimitive, @AmountDiscount, @AmountActual) " + "\r\n";
            queryString = queryString + "       SELECT      InMarketID, SerialID, CollectedTypeID, CommodityID, Quantity, UPrice, UPriceEX, UPriceOD, UPriceCO, DiscountPercent, UPriceDiscount, AmountPrimitive, AmountDiscount, AmountActual FROM InMarketDetail WHERE (InMarketID = @InMarketID) AND (SerialID = @SerialID) " + "\r\n";

            SQLDatabase.CreateStoredProcedure("InMarketDetailInsert", queryString);



            queryString = " @InMarketID int, @SerialID int,	@CollectedTypeID int, @CommodityID int, @Quantity float, @UPrice float,	@UPriceEX float, @UPriceOD float, @UPriceCO float, @DiscountPercent float, @UPriceDiscount float, @AmountPrimitive float, @AmountDiscount float, @AmountActual float " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      InMarketDetail SET CollectedTypeID = @CollectedTypeID, CommodityID = @CommodityID, Quantity = @Quantity, UPrice = @UPrice, UPriceEX = @UPriceEX, UPriceOD = @UPriceOD, UPriceCO = @UPriceCO, DiscountPercent = @DiscountPercent, UPriceDiscount = @UPriceDiscount, AmountPrimitive = @AmountPrimitive, AmountDiscount = @AmountDiscount, AmountActual = @AmountActual WHERE InMarketID = @InMarketID AND SerialID = @SerialID " + "\r\n";
            queryString = queryString + "       SELECT      InMarketID, SerialID, CollectedTypeID, CommodityID, Quantity, UPrice, UPriceEX, UPriceOD, UPriceCO, DiscountPercent, UPriceDiscount, AmountPrimitive, AmountDiscount, AmountActual FROM InMarketDetail WHERE InMarketID = @InMarketID AND SerialID = @SerialID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("InMarketDetailUpdate", queryString);



            queryString = " @InMarketID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM InMarketDetail WHERE InMarketID = @InMarketID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("InMarketDetaillDelete", queryString);



            /// <summary>
            /// Check for save conflict
            /// </summary>
            queryArray = new string[0];
            SQLDatabase.CreateProcedureToCheckExisting("InMarketSaveConflict", queryArray);



            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[1];
            queryArray[0] = " SELECT TOP 1 InMarketID FROM InMarketMaster WHERE InMarketID = @FindIdentityID AND Approved = 1 ";

            SQLDatabase.CreateProcedureToCheckExisting("InMarketApproved", queryArray);



            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[0];//@lInMarketID Int, @lInMarketDate DateTime
            //-----queryArray[0] = " SELECT TOP 1 PerformanceID FROM ListSalesmenInMarketMaster WHERE InMarketFrom <= @lInMarketDate AND InMarketTo >= @lInMarketDate                               WHERE MarketingIncentiveID = @FindIdentityID ";

            SQLDatabase.CreateProcedureToCheckExisting("InMarketEditable", queryArray);

        }



    }
}
